[CHAPTER 01. Row-Level Lock 동작 과정]
1. GDB를 통한 함수 호출 분석


DROP TABLE IF EXISTS rowlock_test;
CREATE TABLE rowlock_test(
    a INTEGER PRIMARY KEY,
    b TEXT,
    c NUMERIC
);

INSERT INTO rowlock_test
VALUES
    (1, 'row1', 100.00),
    (2, 'row2', 200.00),
    (3, 'row3', 300.00);
    
SELECT * 
FROM   rowlock_test
FOR UPDATE;    


[CHAPTER 02. BufferPin과 Vacuum]
2. 테스트 과정

DROP TABLE IF EXISTS lock_test;
CREATE TABLE lock_test (
    a INTEGER NOT NULL,
    b TEXT
);

INSERT INTO lock_test
SELECT   g AS a,
         'TEST_pin'
FROM     generate_series(1, 30) AS g;

2-1. Session 1 - Cursor Open 및 Buffer Pin 설정

BEGIN;
DECLARE c CURSOR FOR
SELECT   *
FROM     lock_test;

FETCH c;

SELECT   *
FROM     pg_buffercache
WHERE    relfilenode = pg_relation_filenode('lock_test');

2-2.  Session 2 - 동일 Buffer에 Pin 설정

BEGIN;
DECLARE c CURSOR FOR
SELECT   *
FROM     lock_test;

FETCH c;

2-3.  Session 3 - VACUUM 및 VACUUM FREEZE 수행

/* 일반 VACUUM */
VACUUM VERBOSE lock_test;


/* VACUUM FREEZE */
VACUUM FREEZE VERBOSE lock_test;

2-4. Buffer 상태 및 Table Age 변화 관찰

/* VACUUM 직후 상태 확인 */
SELECT   age(relfrozenxid)
FROM     pg_class
WHERE    oid = 'lock_test'::regclass;

SELECT   *
FROM     pg_buffercache
WHERE    relfilenode = pg_relation_filenode('lock_test');


/* Session 1 - COMMIT 수행 후 변화 */
SELECT   *
FROM     pg_buffercache
WHERE    relfilenode = pg_relation_filenode('lock_test');


/* Session 2 - COMMIT 수행 VACUUM FREEZE 재개 -> VACUUM FREEZE 완료 후 최종 상태 */
SELECT   age(relfrozenxid)
FROM     pg_class
WHERE    oid = 'lock_test'::regclass;

SELECT   *
FROM     pg_buffercache
WHERE    relfilenode = pg_relation_filenode('lock_test');


[CHAPTER 03. Buffer Manager 동작 과정]
3. GDB를 통한 함수 호출 분석

DROP TABLE IF EXISTS tt;
CREATE TABLE tt AS
SELECT   no AS number,
         md5(random()::text) AS random_text
FROM     pg_catalog.generate_series(1, 10000000) AS no;

CREATE INDEX tt_idx ON tt (number);
 
SELECT number, random_text
FROM   tt
WHERE  number = 3333333;


[CHAPTER 04. Foreign Key와 Buffer Lock]
2. 테스트 과정

DROP TABLE IF EXISTS parent_test, child_test;
CREATE TABLE IF NOT EXISTS parent_test (
    a BIGSERIAL PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS child_test (
    a BIGSERIAL PRIMARY KEY,
    b TEXT,
    c INT,
    FOREIGN KEY (c) REFERENCES parent_test (a)
);

INSERT INTO parent_test
SELECT   generate_series(1, 10);

INSERT INTO child_test
SELECT   generate_series(1, 10),          
         lpad('y', 10, 'y'),              
         generate_series(1, 10);         
		 
2-1. UPDATE 실행

SELECT pg_backend_pid();

UPDATE child_test SET c = 1 WHERE a = 1;       


[CHAPTER 05. WAL Record 생성 및 저장 과정]
2. GDB를 통한 함수 호출 흐름

CREATE TABLE waltest (
    a INTEGER
);

INSERT INTO waltest VALUES(1);  		 
